package com.cpi.cornpp.balanceversion.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.balanceversion.dto.BalanceVersionDTO;
import com.cpi.cornpp.balanceversion.exception.BalanceVersionException;
import com.cpi.cornpp.common.exception.CornPPException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

/**
 * 
 * @description: This Class is used to handle the Balance Version Data Access
 * @author Edgar Joao
 */
public class BalanceVersionDAO extends DBDAO{
	/** Datasource name*/
	private static final String DATASOURCE 	= "cornpp";//$NON-NLS-1$
	private static final String SELECT_LIST_BALANS_VERSIONS = "SELECT MON.MONTH_PERIOD_VERSION_ID, BALANCE_VERSION_ID, AMOUNT, OUT_OF_CREDIT, TAX1, TAX2, BALANCE, (select sum(amount) from po_balances_versions where balance_version_id = BAL.BALANCE_VERSION_ID) AS BALANCE_VERSION FROM MONTHS_PERIODS_VERSIONS MON LEFT JOIN BALANCES_VERSIONS BAL ON MON.MONTH_PERIOD_VERSION_ID = BAL.MONTH_PERIOD_VERSION_ID INNER JOIN MONTHS_PERIODS MP ON MON.MONTH_PERIOD_ID = MP.MONTH_PERIOD_ID WHERE MON.VERSION_ID = ? ORDER BY MP.START_DATE";//$NON-NLS-1$
	private static final String UPDATE_BALANS_VERSIONS = "UPDATE BALANCES_VERSIONS SET AMOUNT = ?, OUT_OF_CREDIT = ?, TAX1 = ?, TAX2 = ?, BALANCE = ? WHERE BALANCE_VERSION_ID = ?";//$NON-NLS-1$
	/**
	 * @author Edgar Joao
	 * @param versionId
	 * @return
	 * @throws BalanceVersionException
	 */
	public List getBalansVersions(int versionId) throws BalanceVersionException{
		List listOfBalansVersions = null;
		PreparedStatement preparedStatement = null;
		BalanceVersionDTO balanceVersionDTO = null;
		ResultSet rs = null;
		try{				
			listOfBalansVersions = new ArrayList(0);			
			preparedStatement = prepareSQL(SELECT_LIST_BALANS_VERSIONS, DATASOURCE);
			preparedStatement.setInt(1, versionId);	
			rs = executeQuery(preparedStatement);			
			while (rs.next()){
				balanceVersionDTO = new BalanceVersionDTO();
				balanceVersionDTO.setMonthPeriodVersionId(rs.getInt(1));
				balanceVersionDTO.setBalanceVersionId(rs.getInt(2));
				balanceVersionDTO.setAmount(rs.getDouble(3));
				balanceVersionDTO.setOutOfCredit(rs.getDouble(4));
				balanceVersionDTO.setTax1(rs.getDouble(5));
				balanceVersionDTO.setTax2(rs.getDouble(6));
				balanceVersionDTO.setBalance(rs.getDouble(7));
				balanceVersionDTO.setBalanceVersion(rs.getDouble(8));
				listOfBalansVersions.add(balanceVersionDTO);
			}	
		}catch (PoolException poolException){
			BalanceVersionException balanceVersionException = null;
			poolException.printStackTrace(System.out);
			balanceVersionException = new BalanceVersionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw balanceVersionException;	
		} catch (SQLException sqlException) {
			BalanceVersionException balanceVersionException = null;
			sqlException.printStackTrace(System.out);
			balanceVersionException = new BalanceVersionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw balanceVersionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return listOfBalansVersions;		
	}
	/**
	 * @author Edgar Joao
	 * @param balansVersionsList
	 * @throws BalanceVersionException
	 */
	public void updateBalansVersions(BalanceVersionDTO dto) throws BalanceVersionException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(UPDATE_BALANS_VERSIONS, DATASOURCE);
			preparedStatement.setDouble(1, dto.getAmount());
			preparedStatement.setDouble(2, dto.getOutOfCredit());
			preparedStatement.setDouble(3, dto.getTax1());
			preparedStatement.setDouble(4, dto.getTax2());
			preparedStatement.setDouble(5, dto.getBalance());
			preparedStatement.setInt(6, dto.getBalanceVersionId());			
			preparedStatement.executeUpdate();
		}catch (PoolException poolException){
			BalanceVersionException balanceVersionException = null;
			poolException.printStackTrace(System.out);
			balanceVersionException = new BalanceVersionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw balanceVersionException;	
		} catch (SQLException sqlException) {
			BalanceVersionException balanceVersionException = null;
			sqlException.printStackTrace(System.out);
			balanceVersionException = new BalanceVersionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_UPDATE);
			throw balanceVersionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
}
